﻿using Hxj.Data;
using RecruitStudents.Utility.Helper;
using RecruitStudents.Utility.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace RecruitStudents.Client.DAL
{
    public class DALBase : Base.BaseService
    {
        public DataTable GetExamMacTable(WhereClip wcp)
        {
            return DbHelper.GetDatabase().From<TB_Exam_Mac>() //选择表
                                       .Select(TB_Exam_Mac._.All)  //设置选择的字段数目 
                                       .Where(wcp)   //条件
                                       .OrderBy(new OrderByClip(TB_Exam_Mac._.MachineName, OrderByOperater.ASC))   //排序
                                       .ToDataTable();   //返回List
        }
        public DataTable GetExamMacByRoomTable(WhereClip wcp)
        {
            return DbHelper.GetDatabase().From<TB_Exam_Mac>() //选择表
                                       .Select(TB_Exam_Mac._.All)  //设置选择的字段数目                                        
                                       .Where(wcp && TB_Exam_Mac._.MacNO.SubQueryNotIn(
                                           DbHelper.GetDatabase().From<TB_Exam_ExamRoomMacMapping>()
                                           .Where(TB_Exam_Mac._.MacNO == TB_Exam_ExamRoomMacMapping._.MacNo)//&& TB_Exam_ExamRoomMacMapping._.ExamRoomNO == strRoomID
                                           .Select(TB_Exam_ExamRoomMacMapping._.MacNo))
                                       )   //条件
                                       .OrderBy(new OrderByClip(TB_Exam_Mac._.MachineName, OrderByOperater.ASC))   //排序
                                       .ToDataTable();   //返回List
        }

        public DataTable GetUserTable(WhereClip wcp)
        {
            return DbHelper.GetDatabase().From<TB_Sys_User>() //选择表 
                                       .LeftJoin<TB_Sys_Role>(TB_Sys_Role._.RoleID == TB_Sys_User._.RoleID)
                                       .Select(TB_Sys_User._.All, TB_Sys_Role._.RoleName)  //设置选择的字段数目 
                                       .Where(wcp)   //条件
                                       .OrderBy(new OrderByClip(TB_Sys_User._.CreateDate, OrderByOperater.DESC))   //排序
                                       .ToDataTable();   //返回List 
        }

        public DataTable GetRoleAuthModuleTable(string strRoleID)
        {
            return DbHelper.GetDatabase().From<TB_Sys_Module>() //选择表 
                                       .InnerJoin<TB_Sys_RoleAuth>(TB_Sys_RoleAuth._.ModuleID == TB_Sys_Module._.ModuleID)
                                       .Select(TB_Sys_Module._.All)  //设置选择的字段数目 
                                       .Where(TB_Sys_RoleAuth._.RoleID == strRoleID)
                                       .OrderBy(new OrderByClip(TB_Sys_Module._.Sort, OrderByOperater.ASC))
                                       .ToDataTable();   //返回List
        }
        public DataTable GetExaminerTable(WhereClip wcp)
        {
            return DbHelper.GetDatabase().From<TB_Exam_Examiner>() //选择表
                .LeftJoin<TB_Sys_SpecialityKinds>(TB_Exam_Examiner._.SpecialityKindsNO == TB_Sys_SpecialityKinds._.SpecialityKindsNO)
                .LeftJoin<TB_Sys_SpecialityKinds_Detail>(TB_Sys_SpecialityKinds_Detail._.SpecialityKinds_DetailNO == TB_Exam_Examiner._.SpecialityKinds_DetailNO)
                                    .Select(TB_Exam_Examiner._.All, TB_Sys_SpecialityKinds._.SpecialityKindsName, TB_Sys_SpecialityKinds_Detail._.SpecialityKinds_DetailName)  //设置选择的字段数目 
                                                                                                                                                                               //.Select(TB_Exam_Examiner._.All, TB_Sys_SpecialityKinds._.SpecialityKindsName)  //设置选择的字段数目 
                                       .Where(wcp)   //条件
                                       .OrderBy(new OrderByClip(TB_Exam_Examiner._.CreateTime, OrderByOperater.DESC))   //排序
                                       .ToDataTable();   //返回List
        }

        public DataTable GetExaminerRoomDataTable(WhereClip wcp)
        {
            return DbHelper.GetDatabase().From<TB_Exam_ExamRoomExaminerMapping>() //选择表
                .LeftJoin<TB_Exam_Examiner>(TB_Exam_ExamRoomExaminerMapping._.ExaminerID == TB_Exam_Examiner._.ExaminerID)
                .LeftJoin<TB_Exam_ExamRoom>(TB_Exam_ExamRoomExaminerMapping._.ExamRoomNO == TB_Exam_ExamRoom._.ExamRoomNO)
                                       .Select(TB_Exam_ExamRoomExaminerMapping._.All, TB_Exam_Examiner._.ExaminerName, TB_Exam_ExamRoom._.ExamRoomName)  //设置选择的字段数目 
                                       .Where(wcp && TB_Exam_Examiner._.IsActive == "1" && TB_Exam_ExamRoom._.IsActive == "1")   //条件
                                       .ToDataTable();   //返回List
        }

        public DataTable GetSysExamPlaceDataTable(WhereClip wcp)
        {
            return DbHelper.GetDatabase().From<TB_Sys_ExamPlace>() //选择表
                                       .LeftJoin<TB_Dic_ExamProvince>(TB_Sys_ExamPlace._.ExamProvince == TB_Dic_ExamProvince._.DicNO)
                                       .Select(TB_Sys_ExamPlace._.All, TB_Dic_ExamProvince._.DicName)  //设置选择的字段数目 
                                       .Where(wcp)   //条件
                                       .ToDataTable();   //返回List
        }

        public DataTable GetExamCalculateTypeFormTable_Detail(WhereClip wcp)
        {
            return DbHelper.GetDatabase().From<TB_Rule_CalculateScoreDetail>() //选择表
                                       .LeftJoin<TB_Sys_SpecialityKinds>(TB_Sys_SpecialityKinds._.SpecialityKindsNO == TB_Rule_CalculateScoreDetail._.SpecialityKindsNO)
                                       .LeftJoin<TB_Sys_SpecialityKinds_Detail>(TB_Sys_SpecialityKinds_Detail._.SpecialityKinds_DetailNO == TB_Rule_CalculateScoreDetail._.SpecialityKinds_DetailNO)
                                       .Select(TB_Rule_CalculateScoreDetail._.All, TB_Sys_SpecialityKinds._.SpecialityKindsName, TB_Sys_SpecialityKinds_Detail._.SpecialityKinds_DetailName)  //设置选择的字段数目 
                                       .Where(wcp)   //条件
                                       .ToDataTable();   //返回List 
        }

        public DataTable GetExamEntretenimientohTable(WhereClip wcp)
        {
            return DbHelper.GetDatabase().From<TB_Exam_Entretenimientoh>() //选择表
                .LeftJoin<TB_Sys_ExamPlace>(TB_Exam_Entretenimientoh._.ExamPlaceNO == TB_Sys_ExamPlace._.ExamPlaceNO)
                .LeftJoin<TB_Sys_SpecialityKinds>(TB_Sys_SpecialityKinds._.SpecialityKindsNO == TB_Exam_Entretenimientoh._.SpecialityKindsNO)
                .LeftJoin<TB_Dic_ExamLevel>(TB_Dic_ExamLevel._.DicNO == TB_Exam_Entretenimientoh._.ExamLevel)
                .LeftJoin<TB_Sys_ExamScoreType>(TB_Sys_ExamScoreType._.ExamScoreTypeID == TB_Exam_Entretenimientoh._.ExamScoreTypeID)
                .Select(TB_Exam_Entretenimientoh._.All, TB_Sys_ExamPlace._.ExamPlaceName, TB_Sys_SpecialityKinds._.SpecialityKindsName
                , TB_Dic_ExamLevel._.DicName.As("ExamLevelName"), TB_Sys_ExamScoreType._.ExamScoreTypeName)  //设置选择的字段数目 
                                       .Where(wcp)   //条件     
                                       .OrderBy(new OrderByClip("ExamPlaceNO,SpecialityKindsNO,ExamLevel", OrderByOperater.ASC))
                                       .ToDataTable();   //返回List
        }

        public DataTable GetExamEntretenimientohRoomTable()
        {
            string strSql = @"select B.ExamPlaceName,C.SpecialityKindsName,D.DicName as ExamLevelName,E.ExamRoomNO,E.ExamRoomName,A.SpecialityKindsNO from 
            TB_Exam_Entretenimientoh as A 
            left join TB_Sys_ExamPlace AS B on A.ExamPlaceNO=B.ExamPlaceNO 
            left join TB_Sys_SpecialityKinds as C on A.SpecialityKindsNO=C.SpecialityKindsNO 
            left join TB_Dic_ExamLevel as D on A.ExamLevel=D.DicNO 
            inner join TB_Exam_ExamRoom as E on A.EntretenimientohID=E.EntretenimientohID and E.IsActive='1' 
            order by A.ExamPlaceNO,A.SpecialityKindsNO,A.ExamLevel";
            return DbHelper.GetDatabase().FromSql(strSql).ToDataTable();
        }

        public DataTable GetRoomMacDataTable(WhereClip wcp)
        {
            return DbHelper.GetDatabase().From<TB_Exam_ExamRoomMacMapping>() //选择表
                .LeftJoin<TB_Exam_ExamRoom>(TB_Exam_ExamRoomMacMapping._.ExamRoomNO == TB_Exam_ExamRoom._.ExamRoomNO)
                .LeftJoin<TB_Exam_Mac>(TB_Exam_Mac._.MacNO == TB_Exam_ExamRoomMacMapping._.MacNo)
                                       .Select(TB_Exam_ExamRoomMacMapping._.All, TB_Exam_Mac._.MachineName, TB_Exam_ExamRoom._.ExamRoomName)  //设置选择的字段数目 
                                       .Where(wcp)   //条件
                                       .OrderBy(new OrderByClip(TB_Exam_Mac._.MachineName, OrderByOperater.ASC))
                                       .ToDataTable();   //返回List
        }

        public DataTable GetSpecialityLevelExamTypeMappingNameTable(WhereClip wcp)
        {
            return DbHelper.GetDatabase().From<TB_Sys_SpecialityLevelExamTypeMapping>() //选择表
                                       .InnerJoin<TB_Sys_SpecialityKinds>(TB_Sys_SpecialityLevelExamTypeMapping._.SpecialityKindsNO == TB_Sys_SpecialityKinds._.SpecialityKindsNO)
                                       .InnerJoin<TB_Dic_ExamLevel>(TB_Sys_SpecialityLevelExamTypeMapping._.ExamLevel == TB_Dic_ExamLevel._.DicNO)
                                       .InnerJoin<TB_Dic_ExamType>(TB_Dic_ExamType._.DicNO == TB_Sys_SpecialityLevelExamTypeMapping._.ExamTypeNo)
                                       .InnerJoin<TB_Rule_CalculateScore>(TB_Sys_SpecialityLevelExamTypeMapping._.CalculateScoreID == TB_Rule_CalculateScore._.CalculateScoreID)
                                       .InnerJoin<TB_Sys_ExamScoreType>(TB_Sys_SpecialityLevelExamTypeMapping._.ExamScoreTypeID == TB_Sys_ExamScoreType._.ExamScoreTypeID)
                                       .Select(TB_Sys_SpecialityLevelExamTypeMapping._.All,
                                               TB_Sys_SpecialityKinds._.SpecialityKindsName,
                                               TB_Dic_ExamLevel._.DicName.As("ExamLevelDicName"),
                                               TB_Dic_ExamType._.DicName.As("ExamTypeName"),
                                               TB_Sys_ExamScoreType._.ExamScoreTypeName.As("ExamScoreTypeName"),
                                               TB_Rule_CalculateScore._.CalculateScoreName.As("CalculateScoreName")
                                       )  //设置选择的字段数目 
                                       .Where(wcp)   //条件
                                       .OrderBy(new OrderByClip(TB_Sys_SpecialityLevelExamTypeMapping._.ExamTypeNo, OrderByOperater.ASC))   //排序
                                       .ToDataTable();   //返回List 
        }

        public DataTable GetExamPlaceSpecialityKindsMappingTable(WhereClip wcp)
        {
            return DbHelper.GetDatabase().From<TB_Sys_ExamPlaceSpecialityKindsMapping>() //选择表
                                       .InnerJoin<TB_Sys_ExamPlace>(TB_Sys_ExamPlaceSpecialityKindsMapping._.ExamPlaceNO == TB_Sys_ExamPlace._.ExamPlaceNO)
                                       .InnerJoin<TB_Sys_SpecialityKinds>(TB_Sys_ExamPlaceSpecialityKindsMapping._.SpecialityKindsNO == TB_Sys_SpecialityKinds._.SpecialityKindsNO)
                                       .Select(TB_Sys_ExamPlaceSpecialityKindsMapping._.All, TB_Sys_ExamPlace._.ExamPlaceName, TB_Sys_SpecialityKinds._.SpecialityKindsName)  //设置选择的字段数目 
                                       .Where(wcp)   //条件
                                       .OrderBy(new OrderByClip("ExamPlaceNO,SpecialityKindsNO", OrderByOperater.ASC))   //排序
                                       .ToDataTable();   //返回List 
        }

        public DataTable GetExamPlaceSpecialityKindsGroupMappingTable(WhereClip wcp)
        {
            return DbHelper.GetDatabase().From<TB_Sys_ExamPlaceSpecialityKindsGroupMapping>() //选择表
                                        .LeftJoin<View_ExamPlaceSpecialityKindsGroupMapping>(TB_Sys_ExamPlaceSpecialityKindsGroupMapping._.GroupNo == View_ExamPlaceSpecialityKindsGroupMapping._.GroupNo)
                                       .Select(TB_Sys_ExamPlaceSpecialityKindsGroupMapping._.All, View_ExamPlaceSpecialityKindsGroupMapping._.ActualAcount.As("XCKSRS"))  //设置选择的字段数目 
                                       .Where(wcp)   //条件
                                       .OrderBy(new OrderByClip(TB_Sys_ExamPlaceSpecialityKindsGroupMapping._.Sort, OrderByOperater.ASC))   //排序
                                       .ToDataTable();   //返回List 
        }
    }
}
